﻿CREATE UNIQUE INDEX WebSearch_Files_UniqueIndex ON [webSearch].[dbo].[Files](id);
GO

CREATE FULLTEXT CATALOG Search_Catalog;
GO

CREATE FULLTEXT INDEX ON [webSearch].[dbo].[Files]
( 
  FullPath,
  Name
) 
KEY INDEX WebSearch_Files_UniqueIndex on Search_Catalog; 
GO

CREATE FUNCTION Search
(      
	@keyword nvarchar(4000)
)
RETURNS TABLE
AS
RETURN
(      
	SELECT *
	FROM [dbo].[Files]
	WHERE NewData = 0 AND CONTAINS (FullPath, @keyword )
)

GO

CREATE PROCEDURE [dbo].[RemoveProtocol]
	-- Add the parameters for the stored procedure here
	@ProtocolId bigint
AS
BEGIN
	DELETE FROM [dbo].[Files] WHERE ProtocolId = @ProtocolId AND NewData=0;
	UPDATE [dbo].[Files] SET NewData=0 WHERE ProtocolId = @ProtocolId AND NewData = 1;
END

GO

